package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.Map;

import util.JDBCUtil;
import domain.ActorJob;
import domain.User;
import ex.DatabaseException;

public class ActorJobDAO {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	private static final String CREATE_ACTORJOB = "insert into actor_job (uid,mid,height,weight,hairColor,eyeColor,skinColor,"
			+ "description , fromAge, isFemale, count) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
	private static final String SELECT_ACTORJOB_BY_MID = "select * from actor_job where mid= ? ";
	private static final String DELETE_ACTORJOB = "delete from actor_job where ajid = ?";
	private static final String EDIT_ACTORJOB = "update actor_job set fromAge = ?, isFemale = ?, height = ?, weight = ?,"
			+ "hairColor = ?, eyeColor = ?, skinColor = ?, description = ?, count = ? where ajid = ?";
	private static final String SELECT_ACTORJOB_BY_AJID = "select * from actor_job where ajid= ? ";
	private static final Timestamp NULL = null;

	ActedDAO adao = new ActedDAO();

	// Selection of the ActorJob Information using the Movie ID in the ActorJob
	// table
	public LinkedList<ActorJob> selectActorJobByMid(long mid)
			throws DatabaseException {

		LinkedList<ActorJob> actorJobs = new LinkedList<ActorJob>();
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_ACTORJOB_BY_MID);
			stmt.setLong(1, mid);

			rs = stmt.executeQuery();
			while (rs.next()) {
				String hairColor = rs.getString("hairColor");
				String eyeColor = rs.getString("eyeColor");
				String skinColor = rs.getString("skinColor");
				int height = rs.getInt("height");
				int weight = rs.getInt("weight");
				String description = rs.getString("description");
				int fromAge = rs.getInt("fromAge");
				Timestamp ts = rs.getTimestamp("timestamp");
				int female = rs.getInt("isFemale");
				int count = rs.getInt("count");
				long ajid = rs.getLong("ajid");
				long uid = rs.getLong("uid");

				Map<User,Integer> users = adao.selectActedUsersByAjid(ajid);

				ActorJob aj = new ActorJob(ajid, uid, mid, hairColor, eyeColor,
						skinColor, height, weight, description, fromAge, ts,
						female, count);
				aj.setApplied(users);
				actorJobs.add(aj);
			}
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving actor job information! "
							+ e.getMessage());
		}
		return actorJobs;
	}

	// Insertion of a new ActorJob to the database
	public void insertActorJob(ActorJob actorJob) throws DatabaseException {

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(CREATE_ACTORJOB);
			stmt.setLong(1, actorJob.getUid());
			stmt.setLong(2, actorJob.getMid());
			stmt.setInt(3, actorJob.getHeight());
			stmt.setInt(4, actorJob.getWeight());
			stmt.setString(5, actorJob.getHairColor());
			stmt.setString(6, actorJob.getEyeColor());
			stmt.setString(7, actorJob.getSkinColor());
			stmt.setString(8, actorJob.getDescription());
			stmt.setInt(9, actorJob.getFromAge());
			stmt.setInt(10, actorJob.getFemale());
			stmt.setInt(11, actorJob.getCount());

			stmt.execute();
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting actor job information! "
							+ e.getMessage());
		}
	}

	// Removal of an ActorJob from the database
	public void deleteActorJob(long ajid) throws DatabaseException {

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(DELETE_ACTORJOB);
			stmt.setLong(1,ajid);

			stmt.execute();
			
			adao.deleteActedByAjid(ajid);
			
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with deleting actor job information! "
							+ e.getMessage());
		}
	}

	// Editing the ActorJob fields
	public void editActorJob(ActorJob actorJob) throws DatabaseException {

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(EDIT_ACTORJOB);
			stmt.setInt(1, actorJob.getFromAge());
			stmt.setInt(2, actorJob.getFemale());
			stmt.setInt(3, actorJob.getHeight());
			stmt.setInt(4, actorJob.getWeight());
			stmt.setString(5, actorJob.getHairColor());
			stmt.setString(6, actorJob.getEyeColor());
			stmt.setString(7, actorJob.getSkinColor());
			stmt.setString(8, actorJob.getDescription());
			stmt.setInt(9, actorJob.getCount());
			stmt.setLong(10, actorJob.getAjid());

			stmt.execute();
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with editing actor job information! "
							+ e.getMessage());
		}
	}

	// Selection of the ActorJob Information using the ActorJob ID in the
	// ActorJob table
	public ActorJob selectActorJobByAjid(long ajid) throws DatabaseException {

		ActorJob aj = null;
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_ACTORJOB_BY_AJID);
			stmt.setLong(1, ajid);

			rs = stmt.executeQuery();
			if (rs.next()) {
				String hairColor = rs.getString("hairColor");
				String eyeColor = rs.getString("eyeColor");
				String skinColor = rs.getString("skinColor");
				int height = rs.getInt("height");
				int weight = rs.getInt("weight");
				String description = rs.getString("description");
				int fromAge = rs.getInt("fromAge");
				// I got an exception about timestamp
				// Timestamp ts = rs.getTimestamp("timestamp");
				Timestamp ts = NULL;
				int female = rs.getInt("isFemale");
				int count = rs.getInt("count");
				long mid = rs.getLong("mid");
				long uid = rs.getLong("uid");

				Map<User,Integer> users = adao.selectActedUsersByAjid(ajid);

				aj = new ActorJob(ajid, uid, mid, hairColor, eyeColor,
						skinColor, height, weight, description, fromAge, ts,
						female, count);
				aj.setApplied(users);
			}
			JDBCUtil.enqueuConn(conn);
			stmt.close();
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving actor job information! "
							+ e.getMessage());
		}
		return aj;
	}
}
